<?php

/**
 * Defines a few commonly needed functions that use the database.
 * connectDB()
 * createNewQREntry($type)
 * getIDByEmail($email,$table)
 * idIsValid($id,$table)
 * getResourceIDsInContainer($container_id)
 * isCurrentlyCheckedOut($resource_id)
 * equipmentIDByTypeAndSN($type, $serial_number)
 * roomIDByFields($building,$floor,$room_number,$room_type)
 * getDetailsOfItemsInTable($table_name)
 * getResourceIDsContainingID($contained_id)
 * isPartOfPackage($id)
 * isAbleToBeTopLevel($id)
 * isTopLevel($id)
 * containerDetailsForResourceInPackage($id)
 * requestorHasResourcesCheckedOut($req_id)
 *
 * Author: Steven Gennaoui
 * Date: 11/4/2012
 */


/**Connect to MySQL database and select the QR Code Checkout
 * System's database. Returns a database handle
 * Original Author: Darren Provine
 * Modifying Author: Steven Gennaoui
 * Date: 10/23/2012
 *
 * USAGE: $dbh = connectDB();
 */
function connectDB()
{
	//* MySQL Server Information
	$hostname = 'localhost';
	$username = 'senior_project';
	$password = 'TheSilence';
	$dbname   = 'cos';

	try 
	{
		$dbh = new PDO("mysql:host=$hostname;
						dbname=$dbname",
						$username,
						$password);
	}
	catch(PDOException $e)
	{
		die('PDO error in "connectDB()": ' . $e->getMessage() );
	}

	return $dbh;
}

/**
 * Creates a new QR Code entry of the type provided in the parameter
 * This should only be called when a new piece of equipment is being added.
 *
 * Params: Type - The type of resource this qr code is associated with
 * Returns: The autoincremented integer value of this new qr_code or 0 if the operation failed
 */
function createNewQREntry($type)
{
	$createEntry = false;
	if( !is_string($type) )
	{	return 0;	}
	
	//The type provided should mirror the table that the resource is going into
	$TABLE_NAMES = array( 1 => "Equipment",
						  2 => "Room");

	foreach($TABLE_NAMES as $name)
	{
		if(strcmp($type,$name) == 0)
		{	$createEntry = true;	}
	}

	if( !$createEntry )
	{	return 0;	}
	else
	{
		$dbh = connectDB();
		$query = "INSERT INTO QR_Code(qr_code_type) VALUES(:type)";
		$stmt = $dbh->prepare( $query );

		$stmt->bindParam(':type',$type);
		$stmt->execute();
		$id = $dbh->lastInsertId();
		$dbh = null;
		$stmt = null;
		return $id;
	}
}


/**
 * Given an email and the table name to search, returns the unique id of
 * the requestor or supplier, if they are active.
 */
function getIDByEmail($email, $table, $checkNotActive)
{
	if( !is_null( $checkNotActive ) )
		$active = "AND is_active=0";
	else
		$active = "AND is_active=1";

	if( !(strcmp($table,"Requestors") == 0 || strcmp($table,"Suppliers") == 0) )
		return -1;
	$dbh = connectDB();
	$query = "SELECT * FROM $table WHERE email=:email ".$active;
	$stmt = $dbh->prepare($query);
	$stmt->bindParam(':email',$email);
	$stmt->execute();
	
	if( $stmt->rowCount() == 1 )
	{
		$data = $stmt->fetch(PDO::FETCH_NUM);
		$dbh = null;
		$stmt = null;
		return $data[0];
	}
	else
	{
		return -1;
	}
}

/** 
 * Given an ID and a table name, determine if the ID exists.
 */
function idIsValid($id,$table)
{
	$TABLE_NAMES = array( "Equipment" => "qr_code",
						  "QR_Code" => "qr_code",
						  "Requestors" => "req_id",
						  "Room" => "qr_code",
						  "Suppliers" => "sup_id");

	if( !array_key_exists($table,$TABLE_NAMES) )
		return false;
	if( !is_numeric($id) )
		return false;

	$dbh = connectDB();
	$id_name = $TABLE_NAMES[$table];
	$query = "SELECT * FROM $table WHERE $id_name=:id";
	$stmt = $dbh->prepare($query);
	$stmt->bindParam(':id',$id);
	$stmt->execute();

	if( $stmt->rowCount() == 1 )
		return true;
	else 
		return false;
}

/**
 * Returns an array of all resource IDs that are contained
 * inside the container provided. Includes recursive links
 * as well. Null if errors.
 * (Ex. If a contains b, b contains c and d, the list will
 *  return a, b, c, and d)
 */
function getResourceIDsInContainer($container_id)
{
	if( !is_numeric($container_id) )
		die("Provided container ID is non-numeric.");
	if( !idIsValid($container_id,"QR_Code") )
		die("Provided container ID is not a valid QR Code.");

	$dbh = connectDB();

	$query = "SELECT contained_qr_code FROM Resource_Packages ".
			 "WHERE container_qr_code = :id";
	$stmt = $dbh->prepare($query);
	$stmt->bindParam(':id', $container_id);
	$stmt->execute();

	if( $stmt->rowCount() == 0 )
	{
		$stmt = null;
		$dbh = null;
		return array( $container_id );
	}
	else
	{
		$ids_in_container = array($container_id); //This id is always considered "inside" itself
		$fetched_values = $stmt->fetchAll(PDO::FETCH_NUM);
		//FETCH_NUM returns array of arrays. each $f is a row in results. 
		foreach( $fetched_values as $f )
		{
			//$f[0] is a column in row $f. There is only one column in our result set.
			// Get the values and append them to the potential containers array
			$potential_containers[] = $f[0];
		}
		//Recursively look for ids
		foreach($potential_containers as $pc)
		{
			$contained_ids = getResourceIDsInContainer($pc);
			//Merge the current list of ids and the recursively discovered ids
			$ids_in_container = array_merge($ids_in_container,$contained_ids);
			//Make sure the ids are unique
			$ids_in_container = array_unique($ids_in_container);
		}
		$stmt = null;
		$dbh = null;
		return $ids_in_container;
	}
}

/**
 * Returns true if the item referenced is currently checked out
 * which is denoted by the has_been_checked_in field of the
 * Resource_Designation table in database and false otherwise.
 */
function isCurrentlyCheckedOut($resource_id)
{
	if( !is_numeric($resource_id) )
		die("Parameter: $resource_id is not numeric!");
	
	$dbh = connectDB();
	$query = "SELECT * FROM Resource_Designation WHERE qr_code=:qr_code AND has_been_checked_in = false";
	$stmt = $dbh->prepare($query);
	$stmt->bindParam(':qr_code',$resource_id);
	$stmt->execute();
	
	if( $stmt->rowCount() == 0 )
		return false;
	else
		return true;
}

/**
 * Takes a type and serial number and looks for a match in the Equipment table.
 * If it exists, return the qr_code associated with it.
 * Otherwise, return -1.
 */
function equipmentIDByTypeAndSN($type, $serial_number)
{
	$dbh = connectDB();
	$query = "SELECT * FROM Equipment WHERE equipment_name=:type AND serial_number=:sn";
	$stmt = $dbh->prepare($query);
	$stmt->bindParam(':type',$type);
	$stmt->bindParam(':sn',$serial_number);
	$stmt->execute();

	if($stmt->rowCount() != 1)
		return -1;
	else
	{
		$result = $stmt->fetch(PDO::FETCH_OBJ);
		return $result->qr_code;
	}
}

/**
 * Takes a building, floor, room number and room type and attempts to match it to an
 * entry in the Room table.
 * If it exists, return the qr_code associated with it.
 * Otherwise, return -1.
 */
function roomIDByFields($building,$floor,$room_number) //,$room_type)
{
	$dbh = connectDB();
	$query = "SELECT * FROM Room WHERE building=:b AND floor=:f AND room_number=:rn";
	$stmt = $dbh->prepare($query);
	$stmt->bindParam(':b',$building);
	$stmt->bindParam(':f',$floor);
	$stmt->bindParam(':rn',$room_number);
	$stmt->execute();

	if($stmt->rowCount() != 1)
		return -1;
	else
	{
		$result = $stmt->fetch(PDO::FETCH_OBJ);
		return $result->qr_code;
	}
}

/**
 * Given the name of a table (Suppliers, Requestors, Equipment, or Room), return all 
 * the active emails from that table.
 */
function getDetailsOfItemsInTable($table_name)
{
    if( strcmp($table_name,"Requestors")!=0 && strcmp($table_name,"Suppliers")!=0 &&
		strcmp($table_name,"Equipment") !=0 && strcmp($table_name,"Room")!=0 )
    {   die("Illegal table name.");     }

    $dbh = connectDB();
    $query = "SELECT * FROM $table_name WHERE is_active=1";
	if( strcmp($table_name,"Equipment") == 0 || strcmp($table_name,"Room") == 0 )
		$query = "SELECT * FROM $table_name WHERE qr_code=ANY".
												"(SELECT qr_code FROM QR_Code ".
												"WHERE is_active=1)";
    $stmt = $dbh->prepare($query);
    $stmt->execute();

    $results = $stmt->fetchAll(PDO::FETCH_ASSOC);

    $dbh=null;
    $stmt=null;

    return $results;

}

/**
 * Given a resource ID, find all resources that directly contain the
 * provided resource.
 * Returns an array of IDs, or dies on errors
 */
function getResourceIDsContainingID($contained_id)
{
	if( !is_numeric($contained_id) )
		die("Provided ID was non-numeric.");
	if( !idIsValid($contained_id,"QR_Code") )
		die("Provided ID was not a valid QR_Code");
	
	$dbh = connectDB();
	$query = "SELECT container_qr_code FROM Resource_Packages WHERE contained_qr_code=:id";
	$stmt = $dbh->prepare($query);
	$stmt->bindParam(':id',$contained_id);
	$stmt->execute();
	
	if( $stmt->rowCount() < 1 )
		return array();
	else
	{
		$containers = $stmt->fetchAll(PDO::FETCH_NUM);
	
		foreach($containers as $c)
		{
			$result[] = $c[0];
		}
		return $result;
	}
}

/**
 * Returns true if the provided ID is already part of a package
 * If it is a container or contained it is already part of a package.
 */
function isPartOfPackage($id)
{
	if( !is_numeric($id) )
		die("Provided QR Code is non-numeric.");

	$dbh=connectDB();
	$query = "SELECT * FROM Resource_Packages WHERE container_qr_code=:qr OR contained_qr_code=:qr";
	$stmt = $dbh->prepare($query);
	$stmt->bindParam(':qr',$id);
	$stmt->execute();

	if( $stmt->rowCount() > 0 )
		return true;
	return false;
}

/**
 * A resource is able to be top level if it is not currently part of a package
 * or if it is alread ya top level container.
 */
function isAbleToBeTopLevel($id)
{
	if( !is_numeric($id) )
		die("Provided QR Code is non-numeric.");
	
	$dbh = connectDB();
	$query = "SELECT * FROM Resource_Packages WHERE contained_qr_code = :qr";
	$stmt = $dbh->prepare($query);
	$stmt->bindParam(':qr',$id);
	$stmt->execute();

	if( $stmt->rowCount() == 0 )
		return true;
	return false;
}

function isTopLevel($id)
{
	if( !is_numeric($id) )
		die("Provided QR Code is non-numeric.");

	$dbh = connectDB();
	$query = "SELECT * FROM Resource_Packages WHERE container_qr_code=:qr";
	$stmt = $dbh->prepare($query);
	$stmt->bindParam(':qr',$id);
	$stmt->execute();
	
	if( $stmt->rowCount() > 0 )
		return true;
	return false;
}

function containerDetailsForResourceInPackage($id)
{
	if( !is_numeric($id) )
		die("Provided QR Code is non-numeric");

	$dbh = connectDB();
	$query = "SELECT * FROM Resource_Packages WHERE contained_qr_code=:qr";
	$stmt = $dbh->prepare($query);
	$stmt->bindParam(':qr',$id);
	$stmt->execute();

	//The resource is not part of a packge.
	if($stmt->rowCount() != 1)
		return;
	else
	{
		$container_id = $stmt->fetch(PDO::FETCH_ASSOC);
		$container_id = $container_id['container_qr_code'];

		$query = "SELECT * FROM QR_Code WHERE qr_code=:qr";
		$stmt = $dbh->prepare($query);
		$stmt->bindParam(':qr',$container_id);
		$stmt->execute();

		if( $stmt->rowCount() == 1 )
		{
			$resource_type = $stmt->fetch(PDO::FETCH_ASSOC);
			$resource_type = $resource_type['qr_code_type'];

			$query = "SELECT * FROM $resource_type WHERE qr_code=:qr";
			$stmt = $dbh->prepare($query);
			$stmt->bindParam(':qr',$container_id);
			$stmt->execute();

			return formatDetails($stmt->fetch(PDO::FETCH_ASSOC));
		}
	}
}

function formatDetails($assoc_array_resource_details)
{
	//If the building field is set, it's a room
	if( isset($assoc_array_resource_details['building']) )
	{
		$qr_code = $assoc_array_resource_details['qr_code'];
		$building = $assoc_array_resource_details['building'];
		$floor = $assoc_array_resource_details['floor'];
		$room_number = $assoc_array_resource_details['room_number'];
		$room_type = $assoc_array_resource_details['room_type'];

		$result = "QR Code: $qr_code\n".
                  "Building Name: $building\n".
                  "Floor Number: $floor\n".
                  "Room Number: $room_number\n".
                  "Room Type: $room_type\n\n";
	}
	else
	{
		$qr_code = $assoc_array_resource_details['qr_code'];
		$equipment_name = $assoc_array_resource_details['equipment_name'];
		$serial_number = $assoc_array_resource_details['serial_number'];
		$condition = $assoc_array_resource_details['current_condition'];
		
		$result = "QR Code: $qr_code\n".
				  "Equipment Name: $equipment_name\n".
				  "Serial Number: $serial_number\n".
				  "Current Condition: $condition\n\n";
	}
	return $result;
}

/**
 * Returns true if requestor has items checekd out.
 * False otherwise.
 */
function requestorHasResourcesCheckedOut($req_id)
{
	if( !idIsValid($req_id,"Requestors") )
		die("Requestor ID is not valid.");
	$dbh = connectDB();
	$query = "SELECT * FROM Resource_Designation WHERE requestor_id=:rid AND has_been_checked_in=0";
	$stmt = $dbh->prepare($query);
	$stmt->bindParam(':rid',$req_id);
	$stmt->execute();

	if( $stmt->rowCount() > 0 )
		return true;
	return false;
}

?>
